package com.situ.web.dao.Impl;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.web.dao.IStudentDao;
import com.situ.web.entity.Banji;
import com.situ.web.entity.Student;
import com.situ.web.service.IStudentService;
import com.situ.web.util.JDBCUtil;
import com.situ.web.vo.StudentBanjiVO;

public class StudentDaoImpl implements IStudentDao{

	@Override
	public ArrayList<StudentBanjiVO> selectAll() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		ArrayList<StudentBanjiVO> list = new ArrayList<StudentBanjiVO>();
		try {
			connection = JDBCUtil.getConnection();
//			String sql = "SELECT s.id 'studentId',s.name,s.age,s.gender,b.name 'banjiName' FROM student AS s INNER JOIN banji AS b ON s.banji_id=b.id";
			String sql = "SELECT s.id 'studentId',s.name,s.age,s.gender,b.name 'banjiName',GROUP_CONCAT(c.name) 'courseName',GROUP_CONCAT(c.credit) 'credit' FROM student AS s INNER JOIN banji AS b ON s.banji_id=b.id INNER JOIN banji_course AS bc ON b.id=bc.banji_id INNER JOIN course AS c ON bc.course_id=c.id GROUP BY s.id;";
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("studentId");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				String banjiName = resultSet.getString("banjiName");
				String courseName = resultSet.getString("courseName");
				int credit = resultSet.getInt("credit");
				StudentBanjiVO student = new StudentBanjiVO(id, name, age, gender, banjiName, courseName, credit);
				list.add(student);
			}
			for (StudentBanjiVO student : list) {
				System.out.println(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		return list;
	}
	@Override
	public int delete(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "DELETE FROM student WHERE id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			count = statement.executeUpdate();
			System.out.println(statement);
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		return count;
	}
	@Override
	public int insert(StudentBanjiVO student) {
		int count = 0;
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO student(`id`,`name`,`age`,`gender`,`banji_id`) VALUES(?,?,?,?,?)";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, student.getId());
			statement.setString(2, student.getName());
			statement.setInt(3, student.getAge());
			statement.setString(4, student.getGender());
			statement.setInt(5, student.getBanjiId());
			count = statement.executeUpdate();
			System.out.println(statement);
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		return count;
	}
	@Override
	public Student selectId(int selectId) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Student student = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`age`,`gender` FROM `student` WHERE `id`=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1,	selectId);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				student = new Student(id, name, age, gender);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		return student;
	}
	@Override
	public int update(StudentBanjiVO student) {
		Connection connection = null;
		PreparedStatement statement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "UPDATE student SET `name`=?,`age`=?,gender=?,banji_id=? WHERE id=?";
			statement = connection.prepareStatement(sql);
			statement.setString(1, student.getName());
			statement.setInt(2, student.getAge());
			statement.setString(3, student.getGender());
			statement.setInt(4, student.getBanjiId());
			statement.setInt(5,student.getId());
			count = statement.executeUpdate();
			System.out.println(statement);
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		return count;
	}
	@Override
	public ArrayList<StudentBanjiVO> selectName(String selectName) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		ArrayList<StudentBanjiVO> list = new ArrayList<StudentBanjiVO>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT s.id 'studentId',s.name,s.age,s.gender,b.name 'banjiName' FROM student AS s INNER JOIN banji AS b ON s.banji_id=b.id WHERE	s.name like ?";
			statement = connection.prepareStatement(sql);
			statement.setString(1, "%" + selectName + "%");
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("studentId");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				String banjiName = resultSet.getString("banjiName");
				StudentBanjiVO student = new StudentBanjiVO(id, name, age, gender, banjiName);
				list.add(student);
			}
			for (StudentBanjiVO student : list) {
				System.out.println(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		return list;
	}
	@Override
	public ArrayList<Banji> selectBanjiName() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		ArrayList<Banji> list = new ArrayList<Banji>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name` FROM banji";
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while(resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				Banji banji = new Banji(id, name);
				list.add(banji);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		return list;
	}

}
